import pymysql


class Database:
    def __init__(self, host, dbname,user,password):
        self.host = host
        self.port = 3306
        self.user = user
        self.passwd = password
        self.db = dbname
        self.dbtype = 'mysql'
        self.conn = None
        self.cur = None
        self.charset = 'utf8'

    def get_conn(self):
        if self.dbtype == 'mysql':
            return pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
        else:
            return False

    def get_cursor(self):
        try:
            self.conn = self.get_conn()
            self.cur = self.conn.cursor()
            return True
        except Exception as err:
            print('db %s failed,Message:%s' % (self.dbtype, err))
            return False

    def close(self):
        self.cur.close()
        self.conn.close()

    def select_table(self,sql):
        try:
            self.cur.execute(sql)
            columns = [col[0] for col in self.cur.description]
            return [
                dict(zip(columns, row))
                for row in self.cur.fetchall()
                ]
            # return rows
        except Exception as err:
            print('Message:%s' % (err))

    def insert_table(self, sql):
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as err:
            self.conn.rollback()
            print('Message:%s' % err)

    def delete_table(self, sql):
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as err:
            print('Message:%s' % err)

    def callsp(self, sp_name):
        try:
            self.cur.callproc(sp_name)
            self.conn.commit()
            # print(111111111111)
            return True
        except Exception as e:
            print(e)
            # print(222222222222)
            return False

    def callsp1(self, sp_name, a):
        try:
            self.cur.callproc(sp_name, (a))
            self.conn.commit()
            return True
        except Exception as e:
            print(e)
            print(222222222222)
            return False


def my_select(sql,host,db_name,user,password):

    db = Database(host=host, dbname=db_name,user=user,password=password)
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


def my_insert(sql):
    db = Database(host='10.129.7.200', dbname="db_MC",user="user1",password="Beaconuser1!")
    db.get_conn()
    db.get_cursor()
    db.insert_table(sql)
    db.close()


def my_delete(sql):
    db = Database(host='10.129.7.200', dbname="db_MC", user="user1", password="Beaconuser1!")
    db.get_conn()
    db.get_cursor()
    db.delete_table(sql)
    db.close()


def c_producer(name):
    db = Database(host='10.129.7.200', dbname="db_MC", user="user1", password="Beaconuser1!")
    db.get_conn()
    db.get_cursor()
    db.callsp(name)
    db.close()